package util;

import java.io.IOException;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;

import com.mysql.jdbc.ResultSetMetaData;

//import edu.taru.dao.RowHandleMapper;
import edu.taru.pojo.User;
import util.SqlHelper;
/**
 * JDBC链接mysql数据库查询
 * @author 
 *
 */
public class SqlHelper {
	private static Properties properties=new Properties();
	/*private static final String url="";
    private static final String user="";
    private static final String password="";*/
    //static Connection conn=null;全局唯一，线程不安全
    static ThreadLocal<Connection> local=new ThreadLocal<Connection>();
    //读
    static{
    	try {
    		properties.load(SqlHelper.class.getClassLoader().getResourceAsStream("jdbc.properties"));
    	} catch (IOException e) {
    		e.printStackTrace();
    	}
    }
    static{
	try {
		Class.forName(properties.getProperty("driver"));
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	}
}
	public static Connection openConnection(){
		Connection conn=local.get();
		try {
		if(conn==null||conn.isClosed()){		
		conn=DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("user"),properties.getProperty("password"));
		local.set(conn);
		    }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	/**
	 * 
	 * @param sql
	 * @param objects
	 * @return
	 */
	public static List<HashMap<String,Object>> select1(String sql,Object ... objects){
		Connection conn=SqlHelper.openConnection();
		PreparedStatement pre=null;
		ResultSet re=null;
		List<HashMap<String,Object>>rows=new ArrayList<HashMap<String,Object>>();
		try {
			pre=conn.prepareStatement(sql);
		    if(pre!=null){
		    	for(int i=0;i<objects.length;i++){
		    		pre.setObject(i+1, objects[i]);	
		    	}
		    }	
		   re= pre.executeQuery();
		   java.sql.ResultSetMetaData remeta=re.getMetaData();
		   //获取列头元素
		   int length =remeta.getColumnCount();
		   
		   //获取列的数量
		   while(re.next()){
			   HashMap<String,Object> map=new HashMap<String,Object>();
			   for(int i=0;i<length;i++){
				   String columnlable=remeta.getColumnLabel(i+1);
				   map.put(columnlable, re.getObject(columnlable));
				   //根据列的标签名获取数据
			   }
			   rows.add(map);
		   }
		   //数据解析完毕，调用了实现子类Mapping的方法
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("执行出错",e);
		}finally{
			if(re!=null){
				try {
					re.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return rows;
		
	
		
	}
	/**
	 * 查询
	 * @param sql
	 * @param handerMapper
	 * @param objects
	 * @return
	 */
	public static <T>List<T> select(String sql,RowHandlerMappers handerMapper,Object...objects){
		Connection conn=SqlHelper.openConnection();
		PreparedStatement pre=null;
		ResultSet re=null;
		List<T>rows=null;
		try {
			pre=conn.prepareStatement(sql);
		    if(pre!=null){
		    	for(int i=0;i<objects.length;i++){
		    		pre.setObject(i+1, objects[i]);	
		    	}
		    }	
		   re= pre.executeQuery();
		   rows=handerMapper.mapping(re);
		   //数据解析完毕，调用了实现子类Mapping的方法
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(re!=null){
				try {
					re.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return rows;
		
	}
	/**
	 * 更新
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int update(String sql,Object...params) {
		Connection conn = SqlHelper.openConnection();
		PreparedStatement pst = null;
		int rows = 0;
		try {
			pst = conn.prepareStatement(sql);
			if(params!=null) {
				for(int i = 0;i<params.length;i++) {
					pst.setObject(i+1, params[i]);
				}
			}
			rows = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(pst!=null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return rows;
	}
	
	
	/**
	 * 关闭数据库连接
	 */
	public void close(){
		Connection conn=local.get();
		if(conn!=null){
			try {
				conn.close();
				local.remove();
				conn=null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	

}

